#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.switch_code import *
from shop_conn import *
from cate_city_tools import *
from lib.load_data import *
import json
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')

def get_order_data(date):
    
    print "get_order_data bigin : %s"%date
    
    conn = set_order_conn()
    stat_conn = set_stats_conn()
    prod_conn = set_product_conn()
    shop_conn = set_shop_conn()
    
    stat_table = "order_action"
    line_list = []
    
    #get data
    query = "select a.orderid,a.refer,a.buyerid,a.buyernick,a.phone,b.productid,a.sellernike,a.cityid,a.state,a.createdate,a.paydate,b.num,c.usednum,c.lifestate,a.ordertotal,b.resumestate,b.price,b.turnoverprice,d.paytype,b.id,a.sellerid "
    query += " from t_order a left join t_orderproduct b "
    query += " on a.orderid=b.orderid "
    query += " left join t_orderticket c "
    query += " on a.orderid=c.orderid "
    query += " left join t_orderpay d "
    query += " on a.orderid=d.orderid "
    query += "where a.refer<>4 and d.paystate=1 "      # you xiao de dingdan #and a.state<6 

    items = conn.get_results(query)
    old_order_id = 0
    for item in items:
        order_id = item[0]
        if order_id==old_order_id:
            continue;
        else:
            old_order_id = order_id
            
        refer = item[1]
        buyer_id=item[2]
        buyer_nick = item[3]
        phone = item[4]
        prod_id = item[5]
        prod_nick = item[6]
        source_city_id = item[7]
        state=item[8]
        create_date=item[9]
        pay_date =item[10]
        num = item[11]
        used_num =item[12]
        life_state =item[13]
        totalfee = item[14]
        resume_state = item[15]
        price = item[16]
        turnover_price = item[17]
        pay_type = item[18]
        
        sub_orderid=item[19]
        shop_id=item[20]
        
        if life_state == None:
            life_state = 0
        #if refer = 
        if pay_type == None:
            pay_type = -1
        if used_num == None:
            used_num =0
        if price == None:
            price = 0
        consume_amount = used_num * price;
        
        #cate id,cityid, updatetime
        query_tmp = "select operatedate from t_orderlog where orderid=%s order by id desc limit 1"%order_id
        items_tmp = conn.get_results(query_tmp)
        update_date = items_tmp[0][0]
        
        cate_id =  0
        local = ''
        prod_nick = ''
        param =''
        query_tmp = "select cateid,local,title,param from t_product where productid=%s"%prod_id
        items_tmp = prod_conn.get_results(query_tmp)
        if len(items_tmp):
            cate_id =  items_tmp[0][0]
            local = items_tmp[0][1]
            prod_nick = items_tmp[0][2]
            param = items_tmp[0][3]
        
        #cate
        #print cate_id
        dc = get_thirth_cat(cate_id,param)    
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
        
        p_cityid = 0
        if local != None:            
            pattern =  re.compile(r'"L":(\w*)')
            matchs = pattern.search(local)
            try:
                p_cityid = matchs.group(1)
            except Exception,e:
                p_cityid = 0
        
        #local
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        if local != None and local !='':
            city = json.loads(local)[0]['L']
            #print city
            dc = get_citys(city)
            if dc != None:
                if len(dc) > 0:
                    city_id = dc[0][0]
                    city_name = dc[0][1]
                if len(dc)>1:
                    area_id = dc[1][0]
                    area_name = dc[1][1]
                if len(dc)>2:
                    local_id= dc[2][0]
                    local_name = dc[2][1]
                
        source_city_id,source_city_name = get_city(source_city_id)
        
        shop_type = 0
        query_tmp = "select freeOrPay from shop where id=%s"%shop_id
        items_tmp = shop_conn.get_results(query_tmp)
        if len(items_tmp)>0:
            shop_type = items_tmp[0][0]
        
        #comment
        query_tmp = "select type,count(1) from comment where type>0 and commenttype=2 and suborderid=%s group by type order by type desc"%order_id
        items_tmp = shop_conn.get_results(query_tmp)
        
        good_count=0;
        mid_count = 0
        bad_count =0
        comment_rate = 0
        
        for item in items_tmp:
            tp = item[0]
            cnt = item[1]
            if tp == 1:
                good_count = cnt
            elif tp == 2:
                mid_count=cnt
            else:
                bad_count=cnt
        tl_count = good_count + mid_count + bad_count
        if good_count > 0:
            comment_rate = good_count/tl_count
            
        param = ["%s"%date,"%s"%order_id,"%s"%shop_id,"%s"%refer,"%s"%buyer_id,"%s"%prod_id,"%s"%prod_nick,"%s"%cat1,"%s"%cat1name,"%s"%cat2,"%s"%cat2name,"%s"%cat3,"%s"%cat3name,"%s"%city_id,"%s"%city_name,"%s"%area_id,"%s"%area_name,"%s"%local_id,"%s"%local_name,"%s"%source_city_id,"%s"%source_city_name,"%s"%state,"%s"%create_date,"%s"%update_date,"%s"%resume_state,"%s"%price,"%s"%num,"%s"%turnover_price,'0',"%s"%pay_date,"%s"%pay_type,"%s"%used_num,"%s"%consume_amount,"%s"%life_state,'0','0','0','0',"%s"%good_count,"%s"%mid_count,"%s"%bad_count,"%s"%comment_rate,"%s"%buyer_nick,"%s"%phone,"%s"%shop_type]
        line_list.append('\t'.join(param))

        
    cont = '\n'.join(line_list)
    path = "./order_data/"
    file_name = "order_action_%s.txt"%date.replace('-',"")
    #print path
    writeTextToFile(path,file_name,"w",cont)
    
    #query = "truncate table %s;"%stat_table
    #stat_conn.execute_sql(query)
    load_data(stat_conn,path+file_name,stat_table,'\t')
    conn.close()
    prod_conn.close()
    stat_conn.close()
    shop_conn.close()
    pass

#logic
def execute(date):
    # get main data
    # from order orderproduct orderticket orderpay orderlog
    get_order_data(date)
    pass
def main():

   #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        execute( stat_date) 
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)


if __name__=='__main__':
    main()